From 3ab416da1e72927d4201e7fc283c949a2a57785c Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 31 Jul 2026 17:49:24 +0000 Subject: [PATCH] SWELLING_SOURCES can only use CYCLE_STATES where SSRank is not NULL --- .../lib/triggers/create/cycle_states.m4 | 64 ++++++++++++- .../lib/triggers/create/swelling_sources.m4 | 93 +++++++++++++++++++ doc/src/tables/swelling_sources.m4 | 8 ++ 3 files changed, 163 insertions(+), 2 deletions(-) diff --git a/db/schemas/lib/triggers/create/cycle_states.m4 b/db/schemas/lib/triggers/create/cycle_states.m4 index 44d3da3..2dcf9fb 100644 --- a/db/schemas/lib/triggers/create/cycle_states.m4 +++ b/db/schemas/lib/triggers/create/cycle_states.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2025, 2026 The Meme Factory, Inc. http://www.karlpinc.com/ dnl dnl This program is free software: you can redistribute it and/or modify dnl it under the terms of the GNU Affero General Public License as published by @@ -28,18 +28,78 @@ CREATE OR REPLACE FUNCTION cycle_states_func () LANGUAGE plpgsql sdb_function_set_search_path AS $$ + DECLARE + a_id swelling_sources.id%TYPE; + a_date swelling_sources.date%TYPE; + a_source swelling_sources.source%TYPE; + a_animid swelling_sources.animid%TYPE; + a_swellingmin swelling_sources.swellingmin%TYPE; + a_swellingmax swelling_sources.swellingmax%TYPE; + BEGIN -- Function for cycle_states insert and update triggers -- - -- AGPL_notice(` --', `2025', + -- AGPL_notice(` --', `2025, 2026', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN + cannot_change(`CYCLE_STATES', `Code') + restrict_special_row(`CYCLE_STATES', `Code', sdb_male_swelling) restrict_special_row(`CYCLE_STATES', `Code', sdb_adolescent_swelling) restrict_special_row(`CYCLE_STATES', `Code', sdb_no_swelling) END IF; + IF TG_OP = 'UPDATE' + AND OLD.ssrank IS NOT NULL + AND NEW.ssrank IS NULL THEN + -- SWELLING_SOURCES can only use Codes where SSRank is not NULL + + SELECT swelling_sources.id, swelling_sources.date + , swelling_sources.source, swelling_sources.animid + , swelling_sources.swellingmin + , swelling_sources.swellingmax + INTO a_id , a_date + , a_source , a_animid + , a_swellingmin + , a_swellingmax + FROM swelling_sources + WHERE swelling_sources.swellingmin IS NOT DISTINCT FROM NEW.code + OR swelling_sources.swellingmax IS NOT DISTINCT FROM NEW.code + -- Ensure a consistent error message + ORDER BY swelling_sources.animid + , swelling_sources.date + , swelling_sources.source + , swelling_sources.id; -- There is no uniqueness constraint + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of CYCLE_STATES' + , DETAIL = 'The SSRank value cannot be made NULL when it' + || ' appears as a SWELLING_SOURCES.SwellingMin' + || ' or a SWELLING_SOURCES.SwellingMax value' + || ': Key (Code) = (' + || NEW.code + || '), Value (AsNum) = (' + || textualize(`NEW.asnum') + || '), Value (SSRank) = (' + || textualize(`NEW.ssrank') + || '): Key (SWELLING_SOURCES.ID) = (' + || a_id + || '), Value (SWELLING_SOURCES.Source) = (' + || a_source + || '), Value (SWELLING_SOURCES.Date) = (' + || a_date + || '), Value (SWELLING_SOURCES.AnimID) = (' + || a_animid + || '), Value (SWELLING_SOURCES.SwellingMin) = (' + || textualize(`a_swellingmin') + || '), Value (SWELLING_SOURCES.SwellingMax) = (' + || textualize(`a_swellingmax') + || ')'; + END IF; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/swelling_sources.m4 b/db/schemas/lib/triggers/create/swelling_sources.m4 index ed92492..add30dd 100644 --- a/db/schemas/lib/triggers/create/swelling_sources.m4 +++ b/db/schemas/lib/triggers/create/swelling_sources.m4 @@ -112,6 +112,99 @@ CREATE OR REPLACE FUNCTION swelling_sources_func () END; END IF; + -- + -- SWELLING_SOURCES can only use CYCLE_STATES.Codes where SSRank is not NULL + -- + + -- Test SwellingMin for a NULL CYCLE_STATES.SSRank + IF TG_OP = 'INSERT' + OR (TG_OP = 'UPDATE' + AND NEW.swellingmin IS NOT NULL + AND NEW.swellingmin IS DISTINCT FROM OLD.swellingmin) THEN + DECLARE + a_asnum cycle_states.asnum%TYPE; + a_description cycle_states.description%TYPE; + + BEGIN + SELECT cycle_states.asnum, cycle_states.description + INTO a_asnum , a_description + FROM cycle_states + WHERE cycle_states.code = NEW.swellingmin + AND cycle_states.ssrank IS NULL; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SWELLING_SOURCES' + , DETAIL = 'Invalid SwellingMin value; the CYCLE_STATES.SSRank' + || ' value cannot be NULL' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.date + || '), Value (SwellingMin) = (' + || NEW.swellingmin + || '), Value (SwellingMax) = (' + || textualize(`NEW.swellingmax') + || '): Key (CYCLE_STATES.Code) = (' + || NEW.swellingmin + || '), Value (CYCLE_STATES.AsNum) = (' + || textualize(`a_asnum') + || '), Value (CYCLE_STATES.SSRank) = (' + || 'NULL' + || '), Value (CYCLE_STATES.Description) = (' + || a_description + || ')'; + END IF; + END; + END IF; + + -- Test Swellingmax for a NULL CYCLE_STATES.SSRank + IF TG_OP = 'INSERT' + OR (TG_OP = 'UPDATE' + AND NEW.swellingmax IS NOT NULL + AND NEW.swellingmax IS DISTINCT FROM OLD.swellingmax) THEN + DECLARE + a_asnum cycle_states.asnum%TYPE; + a_description cycle_states.description%TYPE; + + BEGIN + SELECT cycle_states.asnum, cycle_states.description + INTO a_asnum , a_description + FROM cycle_states + WHERE cycle_states.code = NEW.swellingmax + AND cycle_states.ssrank IS NULL; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SWELLING_SOURCES' + , DETAIL = 'Invalid SwellingMax value; the CYCLE_STATES.SSRank' + || ' value cannot be NULL' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.date + || '), Value (SwellingMin) = (' + || textualize(`NEW.swellingmin') + || '), Value (SwellingMax) = (' + || NEW.swellingmax + || '): Key (CYCLE_STATES.Code) = (' + || NEW.swellingmax + || '), Value (CYCLE_STATES.AsNum) = (' + || textualize(`a_asnum') + || '), Value (CYCLE_STATES.SSRank) = (' + || 'NULL' + || '), Value (CYCLE_STATES.Description) = (' + || a_description + || ')'; + END IF; + END; + + END IF; + RETURN NULL; END; $$; diff --git a/doc/src/tables/swelling_sources.m4 b/doc/src/tables/swelling_sources.m4 index dcac974..0d02a51 100644 --- a/doc/src/tables/swelling_sources.m4 +++ b/doc/src/tables/swelling_sources.m4 @@ -62,6 +62,14 @@ individual is between sdb_min_adolescent_age sdb_min_adolescent_age_units old and sdb_max_swelling_age sdb_max_swelling_age_units old, inclusive of endpoints.\ [#f1]_ +The minimum and maximum swelling codes may be only one of those codes +that are allowed to appear in the |SWELLING_STATES| table. +This means that the |SWELLING_SOURCES.SwellingMin| and +|SWELLING_SOURCES.SwellingMax| values may only be |CYCLE_STATES|.\ +|CYCLE_STATES.Code| values where the |CYCLE_STATES|.\ +|CYCLE_STATES.SSRank| column is non-|null|. + + .. contents:: :depth: 2 -- 2.34.1